﻿using System.Collections.Generic;
using System.Linq;
using System.Text;
using QLDBHYT.VO;
using System.Data;
using System.Data.SqlClient;
namespace QLDBHYT.DAO
{
    class P1hDAO
    {
        private dbConnection conn;
        public P1hDAO()
        {
            conn = new dbConnection();
        }
        public DataTable Search(string table, string condition)
        {
            string query = string.Format("select * from " + table + " where 1 = 1 " + condition + " order by sophieu");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
       
        public DataTable SearchP1DU(string table, string ctid)
        {
            string query = string.Format("select * from " + table + " where ct_id = @p1 order by stt_nh");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@p1", SqlDbType.NVarChar);
            sqlParameters[0].Value = ctid;
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchP1D(string table, string ctid)
        {
            string query = string.Format("select * from " + table + " as a left join dm b on b.ma_hieu = a.ma_hieu and b.stt_nh = a.stt_nh where ct_id = @p1");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@p1", SqlDbType.NVarChar);
            sqlParameters[0].Value = ctid;
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchMaTheTheoNgay(string Ngay, string mathe,string sophieu)
        {
            string query = string.Format("select * from dbo.p1_h where ngay_tt = @ngay and rtrim(mathe) = rtrim(@mathe) and rtrim(sophieu) <> @sophieu");
            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@ngay", SqlDbType.NVarChar);
            sqlParameters[0].Value = Ngay;
            sqlParameters[1] = new SqlParameter("@mathe", SqlDbType.NVarChar);
            sqlParameters[1].Value = mathe.Trim();
            sqlParameters[2] = new SqlParameter("@sophieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = sophieu.Trim();
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearhMathe(string mathe)
        {
            string query = string.Format("select top 1 * from ( "
            + " select ho_ten,gioi_tinh,ngay_sinh,thang_sinh,nam_sinh,dia_chi,tinh_kcb,ma_kcb,noi_kham,"
            + " gtri_tu,gtri_den,ngay_tt from dbo.p1_h where mathe = @mathe"
            + " and ngay_tt = (select max(ngay_tt) from dbo.p1_h where mathe = @mathe)"
            + " and gtri_den >= getdate()"
            + " union all"
            + " select ho_ten,gioi_tinh,ngay_sinh,thang_sinh,nam_sinh,dia_chi,tinh_kcb,ma_kcb,noi_kham,"
            + " gtri_tu,gtri_den,ngay_tt from dbo.p2_h where mathe = @mathe"
            + " and ngay_tt = (select max(ngay_tt) from dbo.p2_h where mathe = @mathe)"
            + " and gtri_den >= getdate()"
            + " ) as t"
            + " order by ngay_tt desc");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@mathe", SqlDbType.NVarChar);
            sqlParameters[0].Value = mathe;
            
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchKhoa()
        {
            string query = string.Format("select ma_khoa,ten_khoa from khoakham");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchTenHieu(int thang, int nam)
        {
            string query = string.Format("select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ktc, bietduoc,nhomthuoc,"
            + "\r\n" + "   isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            + "\r\n" + "   sum(sldauky) + sum(slnhap) - sum(slxuat) - sum(slxuatdc) as slcuoiky"
            + "\r\n" + "   from ("
            + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,a.ktc, "
            + "\r\n" + "   a.duongdung as bietduoc,a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            + "\r\n" + "   isnull(c.sldauky,0) as sldauky,isnull(c.slnhap,0) as slnhap,0 as slxuat,isnull(slxuatdc,0) as slxuatdc "
            + "\r\n" + "   from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu   "
            + "\r\n" + "   left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu and c.thang = @thang and c.nam = @nam "
            + "\r\n" + "   where  b.gia <> 0 group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,  "
            + "\r\n" + "   c.sldauky,c.slnhap,slxuatdc,a.duongdung,a.lieudung,a.ktc "
            + "\r\n" + "   union all "
            + "\r\n" + "   select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,d.ktc, "
            + "\r\n" + "   d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh, "
            + "\r\n" + "   0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc "
            + "\r\n" + "   from p1_h b inner join p1_d a on a.ct_id = b.ct_id  "
            + "\r\n" + "   inner join dm d on d.ma_hieu = a.ma_hieu "
            + "\r\n" + "   left join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu where  a.stt_nh in ('01','03') and  "
            + "\r\n" + "   ngay_tt >= (select dauky from nt_ky where ky = 1) "
            + "\r\n" + "   group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh,d.ktc, "
            + "\r\n" + "   d.duongdung,d.lieudung "
            + "\r\n" + "   union all  "
            + "\r\n" + "   select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,d.ktc, "
            + "\r\n" + "   d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh,  "
            + "\r\n" + "   0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc  "
            + "\r\n" + "   from p2_h b inner join p2_d a on a.ct_id = b.ct_id  "
            + "\r\n" + "   inner join dm d on d.ma_hieu = a.ma_hieu "
            + "\r\n" + "   left join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu where  a.stt_nh in ('01','03') and  "
            + "\r\n" + "   ngay_tt >= (select dauky from nt_ky where ky = 1) "
            + "\r\n" + "   group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh,d.ktc, "
            + "\r\n" + "   d.duongdung,d.lieudung "
            + "\r\n" + "   ) as temps "
            + "\r\n" + "   group by stt_nh,ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,gia,gia_bhxh,ktc, "
            + "\r\n" + "   bietduoc,nhomthuoc "
            + "\r\n" + "   having sum(sldauky) + sum(slnhap) - sum(slxuat) - sum(slxuatdc) >=0 "  
            + "\r\n" + "   order by ten_hieu");
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = thang;
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value =nam;
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchDoiTuong()
        {
            string query = string.Format("SELECT Doituong.* FROM  dbo.doituong Doituong");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public P1hVO UpdateRecord(P1hVO p1hVO, List<P1duVO> lstP1duVO, List<P1dVO> lstP1dVO)
        {
            string query = string.Format("UPDATE dbo.p1_h SET loai=@loai,matinh=@matinh,sophieu=@sophieu,benh_vien=@benh_vien,"
            + " ma_khoa=@ma_khoa,ten_khoa=@ten_khoa,mathe=@mathe,mabn=@mabn,ma_dt=@ma_dt,loai_tt=@loai_tt,vanchuyen=@vanchuyen,loai_tt1=@loai_tt1,"
            + " ho_ten=@ho_ten,gioi_tinh=@gioi_tinh,nam_sinh=@nam_sinh,dia_chi=@dia_chi,tinh_kcb=@tinh_kcb,ma_kcb=@ma_kcb,noi_kham=@noi_kham,"
            + " ngay_kham=@ngay_kham,gtri_tu=@gtri_tu,gtri_den=@gtri_den,ngay_tt=@ngay_tt,ma_icd=@ma_icd,ten_benh=@ten_benh,benhan=@benhan,"
            + " ma_bhxh=@ma_bhxh,lenh_dx=@lenh_dx,noi_den=@noi_den,thaisan=@thaisan,"
            + " ngay_lv=@ngay_lv,user_name=@user_name,excel=@excel,doituong=@doituong,ma_cb=@ma_cb,ma_dv=@ma_dv,dieu_tri=@dieu_tri,"
            + " ket_qua=@ket_qua,BENH_PHU=@BENH_PHU,Da_In=@Da_In,Ngay_Sinh=@Ngay_Sinh,Thang_Sinh=@Thang_Sinh WHERE ct_id=@ct_id");
            SqlParameter[] sqlParameters = new SqlParameter[46];

            sqlParameters[0] = new SqlParameter("@ct_id", SqlDbType.NVarChar);
            sqlParameters[0].Value = p1hVO.Ctid;

            sqlParameters[1] = new SqlParameter("@loai", SqlDbType.NVarChar);
            sqlParameters[1].Value = p1hVO.Loai;

            sqlParameters[2] = new SqlParameter("@matinh", SqlDbType.NVarChar);
            sqlParameters[2].Value = p1hVO.Matinh;

            sqlParameters[3] = new SqlParameter("@sophieu", SqlDbType.NVarChar);
            sqlParameters[3].Value = p1hVO.Sophieu;
            //dat       
            sqlParameters[4] = new SqlParameter("@benh_vien", SqlDbType.NVarChar);
            sqlParameters[4].Value = p1hVO.Benhvien;

            sqlParameters[5] = new SqlParameter("@ten_khoa", SqlDbType.NVarChar);
            sqlParameters[5].Value = p1hVO.Tenkhoa;

            sqlParameters[6] = new SqlParameter("@mathe", SqlDbType.NVarChar);
            sqlParameters[6].Value = p1hVO.Mathe;

            sqlParameters[7] = new SqlParameter("@mabn", SqlDbType.NVarChar);
            sqlParameters[7].Value = p1hVO.Namphah;

            sqlParameters[8] = new SqlParameter("@ma_dt", SqlDbType.NVarChar);
            sqlParameters[8].Value = p1hVO.Madt;

            sqlParameters[9] = new SqlParameter("@loai_tt", SqlDbType.NVarChar);
            sqlParameters[9].Value = p1hVO.Loaitt;

            sqlParameters[10] = new SqlParameter("@vanchuyen", SqlDbType.NVarChar);
            sqlParameters[10].Value = p1hVO.Vanchuyen;

            sqlParameters[11] = new SqlParameter("@loai_tt1", SqlDbType.Decimal);
            sqlParameters[11].Value = p1hVO.Loaitt1;

            sqlParameters[12] = new SqlParameter("@ho_ten", SqlDbType.NVarChar);
            sqlParameters[12].Value = p1hVO.Hoten;

            sqlParameters[13] = new SqlParameter("@gioi_tinh", SqlDbType.Int);
            sqlParameters[13].Value = p1hVO.Gioitinh;

            sqlParameters[14] = new SqlParameter("@nam_sinh", SqlDbType.Int);
            sqlParameters[14].Value = p1hVO.Namsinh;

            sqlParameters[15] = new SqlParameter("@dia_chi", SqlDbType.NVarChar);
            sqlParameters[15].Value = p1hVO.Diachi;
            //dat
            sqlParameters[16] = new SqlParameter("@tinh_kcb", SqlDbType.NVarChar);
            sqlParameters[16].Value = p1hVO.Tinhkcb;

            sqlParameters[17] = new SqlParameter("@ma_kcb", SqlDbType.NVarChar);
            sqlParameters[17].Value = p1hVO.Makcb;

            sqlParameters[18] = new SqlParameter("@noi_kham", SqlDbType.NVarChar);
            sqlParameters[18].Value = p1hVO.Noikham;

            sqlParameters[19] = new SqlParameter("@ngay_kham", SqlDbType.SmallDateTime);
            sqlParameters[19].Value = p1hVO.Ngaykham;

            sqlParameters[20] = new SqlParameter("@gtri_tu", SqlDbType.SmallDateTime);
            sqlParameters[20].Value = p1hVO.Gttu;

            sqlParameters[21] = new SqlParameter("@gtri_den", SqlDbType.SmallDateTime);
            sqlParameters[21].Value = p1hVO.Gtden;
            //cat            
            sqlParameters[22] = new SqlParameter("@ngay_tt", SqlDbType.SmallDateTime);
            sqlParameters[22].Value = p1hVO.Ngaytt;

            sqlParameters[23] = new SqlParameter("@ma_icd", SqlDbType.NVarChar);
            sqlParameters[23].Value = p1hVO.Maicd;
            //thanh toan
            sqlParameters[24] = new SqlParameter("@ten_benh", SqlDbType.NVarChar);
            sqlParameters[24].Value = p1hVO.Tenbenh;

            sqlParameters[25] = new SqlParameter("@benhan", SqlDbType.NVarChar);
            sqlParameters[25].Value = p1hVO.Benhan;

            sqlParameters[26] = new SqlParameter("@ma_bhxh", SqlDbType.NVarChar);
            sqlParameters[26].Value = p1hVO.Mabhxh;

            sqlParameters[27] = new SqlParameter("@lenh_dx", SqlDbType.NVarChar);
            sqlParameters[27].Value = p1hVO.Lenhdx;

            sqlParameters[28] = new SqlParameter("@noi_den", SqlDbType.NVarChar);
            sqlParameters[28].Value = p1hVO.Noiden;

            sqlParameters[29] = new SqlParameter("@ngay_g1", SqlDbType.SmallDateTime);
            sqlParameters[29].Value = p1hVO.Ngay1;

            sqlParameters[30] = new SqlParameter("@ngay_g2", SqlDbType.SmallDateTime);
            sqlParameters[30].Value = p1hVO.Ngay2;

            sqlParameters[31] = new SqlParameter("@ngay_g3", SqlDbType.SmallDateTime);
            sqlParameters[31].Value = p1hVO.Ngay3;


            sqlParameters[32] = new SqlParameter("@ngay_lv", SqlDbType.SmallDateTime);
            sqlParameters[32].Value = p1hVO.Ngaylv;

            sqlParameters[33] = new SqlParameter("@user_name", SqlDbType.NVarChar);
            sqlParameters[33].Value = p1hVO.Usernam;

            sqlParameters[34] = new SqlParameter("@excel", SqlDbType.Decimal);
            sqlParameters[34].Value = p1hVO.Excel;
            //bhyt
            sqlParameters[35] = new SqlParameter("@doituong", SqlDbType.Decimal);
            sqlParameters[35].Value = p1hVO.Doituong;

            sqlParameters[36] = new SqlParameter("@ma_cb", SqlDbType.NVarChar);
            sqlParameters[36].Value = p1hVO.Macb;

            sqlParameters[37] = new SqlParameter("@ma_dv", SqlDbType.NVarChar);
            sqlParameters[37].Value = p1hVO.Madv;

            sqlParameters[38] = new SqlParameter("@dieu_tri", SqlDbType.NVarChar);
            sqlParameters[38].Value = p1hVO.Dieutri;

            sqlParameters[39] = new SqlParameter("@ket_qua", SqlDbType.Decimal);
            sqlParameters[39].Value = p1hVO.Ketqua;

            sqlParameters[40] = new SqlParameter("@BENH_PHU", SqlDbType.NVarChar);
            sqlParameters[40].Value = p1hVO.Benhphu;

            sqlParameters[41] = new SqlParameter("@Da_In", SqlDbType.NVarChar);
            sqlParameters[41].Value = p1hVO.Dain;

            sqlParameters[42] = new SqlParameter("@Ngay_Sinh", SqlDbType.Int);
            sqlParameters[42].Value = p1hVO.Ngaysinh;

            sqlParameters[43] = new SqlParameter("@Thang_Sinh", SqlDbType.Int);
            sqlParameters[43].Value = p1hVO.Thangsinh;

            sqlParameters[44] = new SqlParameter("@ma_khoa", SqlDbType.NVarChar);
            sqlParameters[44].Value = p1hVO.Makhoa;

            sqlParameters[45] = new SqlParameter("@thaisan", SqlDbType.Decimal);
            sqlParameters[45].Value = p1hVO.Thaisan;

            if (conn.executeInsertQuery(query, sqlParameters))
            {
                foreach (P1duVO p1duVO in lstP1duVO)
                {
                    UpdateRecordP1DU(p1duVO);
                }
                foreach (P1dVO p1dVO in lstP1dVO)
                {
                    switch (p1dVO.Flag)
                    {
                        case 0:
                            DeleteRecordP1D(p1dVO);
                            break;
                        case 1:
                            break;
                        case 2:
                            UpdateRecordP1D(p1dVO);
                            break;
                        case 3:
                            InsertRecordP1D(p1dVO);
                            break;

                    }
                    
                }
                return p1hVO;
            }
            else
            {
                return null;
            }
    
            
        }
        public P1hVO InsertRecord(P1hVO p1hVO,List<P1duVO> lstP1duVO,List<P1dVO> lstP1dVO)
        {
            string query = string.Format("INSERT INTO dbo.p1_h (ct_id,loai,matinh,sophieu,benh_vien,ma_khoa,ten_khoa,"
                + " mathe,mabn,ma_dt,loai_tt,vanchuyen,loai_tt1,ho_ten,gioi_tinh,nam_sinh,dia_chi,tinh_kcb,ma_kcb,"
                + " noi_kham,ngay_kham,gtri_tu,gtri_den,ngay_tt,ma_icd,ten_benh,benhan,ma_bhxh,lenh_dx,noi_den,ngay_g1,"
                + " ngay_g2,ngay_g3,thaisan,ngay_lv,user_name,excel,doituong,ma_cb,ma_dv,dieu_tri,ket_qua,BENH_PHU,"
                + " Da_In,Ngay_Sinh,Thang_Sinh) VALUES (@ct_id,@loai,@matinh,@sophieu,@benh_vien,@ma_khoa,@ten_khoa,"
                + " @mathe,@mabn,@ma_dt,@loai_tt,@vanchuyen,@loai_tt1,@ho_ten,@gioi_tinh,@nam_sinh,@dia_chi,@tinh_kcb,@ma_kcb,"
                + " @noi_kham,@ngay_kham,@gtri_tu,@gtri_den,@ngay_tt,@ma_icd,@ten_benh,@benhan,@ma_bhxh,@lenh_dx,@noi_den,@ngay_g1,"
                + " @ngay_g2,@ngay_g3,@thaisan,@ngay_lv,@user_name,@excel,@doituong,@ma_cb,@ma_dv,@dieu_tri,@ket_qua,@BENH_PHU,"
                + " @Da_In,@Ngay_Sinh,@Thang_Sinh)");

            SqlParameter[] sqlParameters = new SqlParameter[46];

            sqlParameters[0] = new SqlParameter("@ct_id", SqlDbType.NVarChar);
            sqlParameters[0].Value = p1hVO.Ctid;

            sqlParameters[1] = new SqlParameter("@loai", SqlDbType.NVarChar);
            sqlParameters[1].Value = p1hVO.Loai;

            sqlParameters[2] = new SqlParameter("@matinh", SqlDbType.NVarChar);
            sqlParameters[2].Value = p1hVO.Matinh;

            sqlParameters[3] = new SqlParameter("@sophieu", SqlDbType.NVarChar);
            sqlParameters[3].Value = p1hVO.Sophieu;
            //dat       
            sqlParameters[4] = new SqlParameter("@benh_vien", SqlDbType.NVarChar);
            sqlParameters[4].Value = p1hVO.Benhvien;

            sqlParameters[5] = new SqlParameter("@ten_khoa", SqlDbType.NVarChar);
            sqlParameters[5].Value = p1hVO.Tenkhoa;
             
            sqlParameters[6] = new SqlParameter("@mathe", SqlDbType.NVarChar);
            sqlParameters[6].Value =p1hVO.Mathe;

            sqlParameters[7] = new SqlParameter("@mabn", SqlDbType.NVarChar);
            sqlParameters[7].Value =p1hVO.Namphah;

            sqlParameters[8] = new SqlParameter("@ma_dt", SqlDbType.NVarChar);
            sqlParameters[8].Value = p1hVO.Madt;

            sqlParameters[9] = new SqlParameter("@loai_tt", SqlDbType.NVarChar);
            sqlParameters[9].Value = p1hVO.Loaitt;

            sqlParameters[10] = new SqlParameter("@vanchuyen", SqlDbType.NVarChar);
            sqlParameters[10].Value = p1hVO.Vanchuyen;
            
            sqlParameters[11] = new SqlParameter("@loai_tt1", SqlDbType.Decimal);
            sqlParameters[11].Value = p1hVO.Loaitt1;

            sqlParameters[12] = new SqlParameter("@ho_ten", SqlDbType.NVarChar);
            sqlParameters[12].Value = p1hVO.Hoten;

            sqlParameters[13] = new SqlParameter("@gioi_tinh", SqlDbType.Int);
            sqlParameters[13].Value = p1hVO.Gioitinh ;

            sqlParameters[14] = new SqlParameter("@nam_sinh", SqlDbType.Int);
            sqlParameters[14].Value = p1hVO.Namsinh;

            sqlParameters[15] = new SqlParameter("@dia_chi", SqlDbType.NVarChar);
            sqlParameters[15].Value = p1hVO.Diachi ;
            //dat
            sqlParameters[16] = new SqlParameter("@tinh_kcb", SqlDbType.NVarChar);
            sqlParameters[16].Value = p1hVO.Tinhkcb;

            sqlParameters[17] = new SqlParameter("@ma_kcb", SqlDbType.NVarChar);
            sqlParameters[17].Value =p1hVO.Makcb;

            sqlParameters[18] = new SqlParameter("@noi_kham", SqlDbType.NVarChar);
            sqlParameters[18].Value = p1hVO.Noikham;

            sqlParameters[19] = new SqlParameter("@ngay_kham", SqlDbType.SmallDateTime);
            sqlParameters[19].Value = p1hVO.Ngaykham;

            sqlParameters[20] = new SqlParameter("@gtri_tu", SqlDbType.SmallDateTime);
            sqlParameters[20].Value =p1hVO.Gttu;

            sqlParameters[21] = new SqlParameter("@gtri_den", SqlDbType.SmallDateTime);
            sqlParameters[21].Value = p1hVO.Gtden;
            //cat            
            sqlParameters[22] = new SqlParameter("@ngay_tt", SqlDbType.SmallDateTime);
            sqlParameters[22].Value = p1hVO.Ngaytt;

            sqlParameters[23] = new SqlParameter("@ma_icd", SqlDbType.NVarChar);
            sqlParameters[23].Value = p1hVO.Maicd;
            //thanh toan
            sqlParameters[24] = new SqlParameter("@ten_benh", SqlDbType.NVarChar);
            sqlParameters[24].Value = p1hVO.Tenbenh;

            sqlParameters[25] = new SqlParameter("@benhan", SqlDbType.NVarChar);
            sqlParameters[25].Value = p1hVO.Benhan;

            sqlParameters[26] = new SqlParameter("@ma_bhxh", SqlDbType.NVarChar);
            sqlParameters[26].Value = p1hVO.Mabhxh;

            sqlParameters[27] = new SqlParameter("@lenh_dx", SqlDbType.NVarChar);
            sqlParameters[27].Value =p1hVO.Lenhdx;

            sqlParameters[28] = new SqlParameter("@noi_den", SqlDbType.NVarChar);
            sqlParameters[28].Value = p1hVO.Noiden;

            sqlParameters[29] = new SqlParameter("@ngay_g1", SqlDbType.SmallDateTime);
            sqlParameters[29].Value = p1hVO.Ngay1;

            sqlParameters[30] = new SqlParameter("@ngay_g2", SqlDbType.SmallDateTime);
            sqlParameters[30].Value = p1hVO.Ngay2;

            sqlParameters[31] = new SqlParameter("@ngay_g3", SqlDbType.SmallDateTime);
            sqlParameters[31].Value = p1hVO.Ngay3;

          
            sqlParameters[32] = new SqlParameter("@ngay_lv", SqlDbType.SmallDateTime);
            sqlParameters[32].Value = p1hVO.Ngaylv;

            sqlParameters[33] = new SqlParameter("@user_name", SqlDbType.NVarChar);
            sqlParameters[33].Value = p1hVO.Usernam;

            sqlParameters[34] = new SqlParameter("@excel", SqlDbType.Decimal);
            sqlParameters[34].Value = p1hVO.Excel;
            //bhyt
            sqlParameters[35] = new SqlParameter("@doituong", SqlDbType.Decimal);
            sqlParameters[35].Value = p1hVO.Doituong;

            sqlParameters[36] = new SqlParameter("@ma_cb", SqlDbType.NVarChar);
            sqlParameters[36].Value = p1hVO.Macb;

            sqlParameters[37] = new SqlParameter("@ma_dv", SqlDbType.NVarChar);
            sqlParameters[37].Value = p1hVO.Madv;

            sqlParameters[38] = new SqlParameter("@dieu_tri", SqlDbType.NVarChar);
            sqlParameters[38].Value = p1hVO.Dieutri;
           
            sqlParameters[39] = new SqlParameter("@ket_qua", SqlDbType.Decimal);
            sqlParameters[39].Value = p1hVO.Ketqua;

            sqlParameters[40] = new SqlParameter("@BENH_PHU", SqlDbType.NVarChar);
            sqlParameters[40].Value =p1hVO.Benhphu;
            
            sqlParameters[41] = new SqlParameter("@Da_In", SqlDbType.NVarChar);
            sqlParameters[41].Value = p1hVO.Dain;

            sqlParameters[42] = new SqlParameter("@Ngay_Sinh", SqlDbType.Int);
            sqlParameters[42].Value = p1hVO.Ngaysinh;

            sqlParameters[43] = new SqlParameter("@Thang_Sinh", SqlDbType.Int);
            sqlParameters[43].Value = p1hVO.Thangsinh;

            sqlParameters[44] = new SqlParameter("@ma_khoa", SqlDbType.NVarChar);
            sqlParameters[44].Value = p1hVO.Makhoa ;

            sqlParameters[45] = new SqlParameter("@thaisan", SqlDbType.Decimal);
            sqlParameters[45].Value = p1hVO.Thaisan;
           
            if (conn.executeInsertQuery(query, sqlParameters))
            {
                foreach (P1duVO p1duVO in lstP1duVO)
                {
                  InsertRecordP1DU(p1duVO);
                }
                foreach (P1dVO p1dVO in lstP1dVO)
                {
                  InsertRecordP1D(p1dVO);
                }
                return p1hVO;
            }
            else
            {
                return null;
            }
        }
        public P1duVO UpdateRecordP1DU(P1duVO p1duVO)
        {

            string query = string.Format("UPDATE dbo.p1_du set tong_tien = @P4 ,tien_bn = @P5  "
                + " where stt_nh = @P1 and ct_id = @P2 and ct_id2 = @P3");
            
            SqlParameter[] sqlParameters = new SqlParameter[5];
            sqlParameters[0] = new SqlParameter("@P1", SqlDbType.NVarChar);
            sqlParameters[0].Value = p1duVO.Sttnh;
            sqlParameters[1] = new SqlParameter("@P2", SqlDbType.NVarChar);
            sqlParameters[1].Value = p1duVO.Ctid;
            sqlParameters[2] = new SqlParameter("@P3", SqlDbType.NVarChar);
            sqlParameters[2].Value = p1duVO.Ctid2;
            sqlParameters[3] = new SqlParameter("@P4", SqlDbType.Decimal);
            sqlParameters[3].Value = p1duVO.Tongtien;
            sqlParameters[4] = new SqlParameter("@P5", SqlDbType.Decimal);
            sqlParameters[4].Value = p1duVO.Tienbn;

            if (conn.executeInsertQuery(query, sqlParameters))
            {
                return p1duVO;
            }
            else
            {
                return null;
            }
        }
        public P1duVO InsertRecordP1DU(P1duVO p1duVO)
        {
            string query = string.Format("INSERT INTO dbo.p1_du (stt_nh,ct_id,ct_id2,tong_tien,tien_bn) "
            + " VALUES (@P1,@P2,@P3,@P4,@P5)");
            SqlParameter[] sqlParameters = new SqlParameter[5];
            sqlParameters[0] = new SqlParameter("@P1", SqlDbType.NVarChar);
            sqlParameters[0].Value = p1duVO.Sttnh;
            sqlParameters[1] = new SqlParameter("@P2", SqlDbType.NVarChar);
            sqlParameters[1].Value = p1duVO.Ctid;
            sqlParameters[2] = new SqlParameter("@P3", SqlDbType.NVarChar);
            sqlParameters[2].Value = p1duVO.Ctid2;
            sqlParameters[3] = new SqlParameter("@P4", SqlDbType.Decimal);
            sqlParameters[3].Value = p1duVO.Tongtien;
            sqlParameters[4] = new SqlParameter("@P5", SqlDbType.Decimal);
            sqlParameters[4].Value = p1duVO.Tienbn;

            if (conn.executeInsertQuery(query, sqlParameters))
            {
                return p1duVO;
            }
            else
            {
                return null;
            }
        }
        public bool DeleteRecordP1D(P1dVO p1dVO)
        {
            string query = string.Format("Delete dbo.p1_d where ct_id = @P1 and ct_id0 = @P2");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@P1", SqlDbType.NVarChar);
            sqlParameters[0].Value = p1dVO.Ctid;
            sqlParameters[1] = new SqlParameter("@P2", SqlDbType.NVarChar);
            sqlParameters[1].Value = p1dVO.Ctid0;

            if (conn.executeInsertQuery(query, sqlParameters))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        public P1dVO UpdateRecordP1D(P1dVO p1dVO)
        {
            string query = string.Format("Update dbo.p1_d set stt_nh = @P3, ma_hieu =@P4 ,ty_le = @P5,ktc = @P6,"
            + " sl = @P7,gia = @P8 ,gia_bhxh = @P9,tong_tien = @P10, "
            + " tien_bhxh = @P11,tien_chenh = @P12,tien_bn = @P13,loai = @P14,BacSy =@P15 ,Khoa = @P16,Chon = @P17 "
            + " where ct_id = @P1 and ct_id0 = @P2");
                       
            SqlParameter[] sqlParameters = new SqlParameter[17];

            sqlParameters[0] = new SqlParameter("@P1", SqlDbType.NVarChar);
            sqlParameters[0].Value = p1dVO.Ctid;
            sqlParameters[1] = new SqlParameter("@P2", SqlDbType.NVarChar);
            sqlParameters[1].Value = p1dVO.Ctid0;
            sqlParameters[2] = new SqlParameter("@P3", SqlDbType.NVarChar);
            sqlParameters[2].Value = p1dVO.Sttnh;
            sqlParameters[3] = new SqlParameter("@P4", SqlDbType.NVarChar);
            sqlParameters[3].Value = p1dVO.Mahieu;
            sqlParameters[4] = new SqlParameter("@P5", SqlDbType.Decimal);
            sqlParameters[4].Value = p1dVO.Tyle;
            sqlParameters[5] = new SqlParameter("@P6", SqlDbType.NVarChar);
            sqlParameters[5].Value = p1dVO.Ktc;
            sqlParameters[6] = new SqlParameter("@P7", SqlDbType.Decimal);
            sqlParameters[6].Value = p1dVO.Sl;
            sqlParameters[7] = new SqlParameter("@P8", SqlDbType.Decimal);
            sqlParameters[7].Value = p1dVO.Gia;
            sqlParameters[8] = new SqlParameter("@P9", SqlDbType.Decimal);
            sqlParameters[8].Value = p1dVO.Giabhxh;
            sqlParameters[9] = new SqlParameter("@P10", SqlDbType.Decimal);
            sqlParameters[9].Value = p1dVO.Tongtien;
            sqlParameters[10] = new SqlParameter("@P11", SqlDbType.Decimal);
            sqlParameters[10].Value = p1dVO.Tienbhxh;
            sqlParameters[11] = new SqlParameter("@P12", SqlDbType.Decimal);
            sqlParameters[11].Value = p1dVO.Tienchenhlech;
            sqlParameters[12] = new SqlParameter("@P13", SqlDbType.Decimal);
            sqlParameters[12].Value = p1dVO.Tien_bn;
            sqlParameters[13] = new SqlParameter("@P14", SqlDbType.Decimal);
            sqlParameters[13].Value = p1dVO.Loai;
            sqlParameters[14] = new SqlParameter("@P15", SqlDbType.NVarChar);
            sqlParameters[14].Value = p1dVO.Bacsy;
            sqlParameters[15] = new SqlParameter("@P16", SqlDbType.NVarChar);
            sqlParameters[15].Value = p1dVO.Khoa;
            sqlParameters[16] = new SqlParameter("@P17", SqlDbType.Decimal);
            sqlParameters[16].Value = p1dVO.Chon;


            if (conn.executeInsertQuery(query, sqlParameters))
            {
                return p1dVO;
            }
            else
            {
                return null;
            }
        }
        public P1dVO InsertRecordP1D(P1dVO p1dVO)
        {
            string query = string.Format("INSERT INTO dbo.p1_d (ct_id,ct_id0,stt_nh,ma_hieu,ty_le,ktc,sl,gia,gia_bhxh,tong_tien, "
            + " tien_bhxh,tien_chenh,tien_bn,loai,BacSy,Khoa,Chon) "
            + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17)");

            SqlParameter[] sqlParameters = new SqlParameter[17];
           
            sqlParameters[0] = new SqlParameter("@P1", SqlDbType.NVarChar);
            sqlParameters[0].Value = p1dVO.Ctid;
            sqlParameters[1] = new SqlParameter("@P2", SqlDbType.NVarChar);
            sqlParameters[1].Value = p1dVO.Ctid0;
            sqlParameters[2] = new SqlParameter("@P3", SqlDbType.NVarChar);
            sqlParameters[2].Value = p1dVO.Sttnh;
            sqlParameters[3] = new SqlParameter("@P4", SqlDbType.NVarChar);
            sqlParameters[3].Value = p1dVO.Mahieu;
            sqlParameters[4] = new SqlParameter("@P5", SqlDbType.Decimal);
            sqlParameters[4].Value = p1dVO.Tyle;
            sqlParameters[5] = new SqlParameter("@P6", SqlDbType.NVarChar);
            sqlParameters[5].Value = p1dVO.Ktc;
            sqlParameters[6] = new SqlParameter("@P7", SqlDbType.Decimal);
            sqlParameters[6].Value = p1dVO.Sl;
            sqlParameters[7] = new SqlParameter("@P8", SqlDbType.Decimal);
            sqlParameters[7].Value = p1dVO.Gia;
            sqlParameters[8] = new SqlParameter("@P9", SqlDbType.Decimal);
            sqlParameters[8].Value = p1dVO.Giabhxh;
            sqlParameters[9] = new SqlParameter("@P10", SqlDbType.Decimal);
            sqlParameters[9].Value = p1dVO.Tongtien;
            sqlParameters[10] = new SqlParameter("@P11", SqlDbType.Decimal);
            sqlParameters[10].Value = p1dVO.Tienbhxh;
            sqlParameters[11] = new SqlParameter("@P12", SqlDbType.Decimal);
            sqlParameters[11].Value = p1dVO.Tienchenhlech;
            sqlParameters[12] = new SqlParameter("@P13", SqlDbType.Decimal);
            sqlParameters[12].Value = p1dVO.Tien_bn;
            sqlParameters[13] = new SqlParameter("@P14", SqlDbType.Decimal);
            sqlParameters[13].Value = p1dVO.Loai;
            sqlParameters[14] = new SqlParameter("@P15", SqlDbType.NVarChar);
            sqlParameters[14].Value = p1dVO.Bacsy;
            sqlParameters[15] = new SqlParameter("@P16", SqlDbType.NVarChar);
            sqlParameters[15].Value = p1dVO.Khoa;
            sqlParameters[16] = new SqlParameter("@P17", SqlDbType.Decimal);
            sqlParameters[16].Value = p1dVO.Chon;


            if (conn.executeInsertQuery(query, sqlParameters))
            {
                return p1dVO;
            }
            else
            {
                return null;
            }
        }
        public bool DeleteRecordP1D(string ctid)
        {
            string query = string.Format("Delete dbo.p1_d where ct_id = @P1");

            SqlParameter[] sqlParameters = new SqlParameter[1];

            sqlParameters[0] = new SqlParameter("@P1", SqlDbType.NVarChar);
            sqlParameters[0].Value = ctid;
           
            if (conn.executeInsertQuery(query, sqlParameters))
            {
                
               return true;
            }
            else
            {
                return false;
            }
        }
        public bool DeleteRecordP1DU(string ctid)
        {
            string query = string.Format("Delete dbo.p1_du where ct_id = @P1");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@P1", SqlDbType.NVarChar);
            sqlParameters[0].Value = ctid;

            if (conn.executeInsertQuery(query, sqlParameters))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool DeleteRecord(string ctid)
        {
            string query = string.Format("Delete dbo.p1_h where ct_id = @P1");
            SqlParameter[] sqlParameters = new SqlParameter[1];

            sqlParameters[0] = new SqlParameter("@P1", SqlDbType.NVarChar);
            sqlParameters[0].Value = ctid;

            if (conn.executeInsertQuery(query, sqlParameters))
            {
                DeleteRecordP1DU(ctid);
                DeleteRecordP1D(ctid);
                return true;
            }
            else
            {
                return false;
            }
        }
        public DataTable SearchBV(string matinh)
        {
            string query = string.Format("select rtrim(ma_bv) as ma_bv,ten_bv from dm_bv where ma_tinh = @p1");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@p1", SqlDbType.NVarChar);
            sqlParameters[0].Value = matinh;
            return conn.executeSelectQuery(query, sqlParameters);
        }

                    





        //}
        
    }
}
